create table Tdb_version(
	version integer not null
);
insert into Tdb_version (version) values (1);

/**************************************************************
 *	Languages
 *
 */
create table Tl10n (
	id					bigserial primary key,
	code				varchar(16) not null unique,
	name				varchar(160) not null,
	is_default			integer not null
);

/**************************************************************
 *	File types
 */
create table Tmediums (
	id					bigserial primary key,
	code				varchar(16) not null unique,
	ordering			bigserial not null unique,
	name				varchar(128) not null unique
);

/**************************************************************
 *	File types
 */
create table Tfile_types (
	id					bigserial primary key,
	code				varchar(16) not null unique,
	name				varchar(128) not null
);

/**************************************************************
 * File folders
 */
create table Tfile_folders (
	id					bigserial primary key,
	code				varchar(64) unique,
	parent_id			bigint null,
	user_cr_id			bigint not null,
	date_cr				timestamp default now() not null,
	date_mo				timestamp default now() not null,
	ordering			bigserial unique not null,
	foreign key			(parent_id) references Tfile_folders
);
insert into Tfile_folders (code,user_cr_id) values ('avatars',1);
insert into Tfile_folders (code,user_cr_id) values ('galleries',1);
insert into Tfile_folders (code,user_cr_id) values ('news',1);
insert into Tfile_folders (code,user_cr_id) values ('images',1);
insert into Tfile_folders (code,user_cr_id) values ('market_items',1);

create table Tfile_folders_l10n (
	id					bigserial primary key,
	fid_id				bigint not null,
	l10n_id				bigint not null,
	name				varchar(128) not null,
	foreign key			(fid_id) references Tfile_folders,
	foreign key			(l10n_id) references Tl10n
);
create unique index Ifile_folders_l10n on Tfile_folders_l10n (fid_id,l10n_id);

/**************************************************************
 * Files
 */
create table Tfiles (
	id					bigserial primary key,
	type_id				bigint not null,
	file_folder_id		bigint not null,
	user_cr_id			bigint null,
	date_cr				timestamp default now() not null,
	date_mo				timestamp default now() not null,
	orig_filename		varchar(256) not null,
	ext					varchar(16),
	width				integer,
	height				integer,
	filesize			integer,
	seconds				integer,
	ordering			bigserial unique not null,
	foreign key			(type_id) references Tfile_types,
	foreign key			(file_folder_id) references Tfile_folders
);

create table Tfiles_l10n (
	id					bigserial primary key,
	fid_id				bigint not null,
	l10n_id				bigint not null,
	name				varchar(128) not null,
	foreign key			(fid_id) references Tfiles,
	foreign key			(l10n_id) references Tl10n
);
create unique index Ifiles_l10n on Tfiles_l10n (fid_id,l10n_id);

/**************************************************************
 *
 */
create table Tsexes (
	id					bigserial primary key,
	code				varchar(16) not null unique
);

create table Tsexes_l10n (
	id					bigserial primary key,
	fid_id				bigint not null,
	l10n_id				bigint not null,
	name				varchar(128) not null,
	foreign key			(fid_id) references Tsexes,
	foreign key			(l10n_id) references Tl10n
);
create unique index Isexes_l10n on Tsexes_l10n (fid_id,l10n_id);

/**************************************************************
 *
 */
create table Tusers (
	id					bigserial primary key,
	login				varchar(64) not null unique,
	first_name			varchar(64),
	second_name			varchar(64),
	last_name			varchar(64),
	calc_full_name		varchar(256),
	password			varchar(64),
	enabled				integer not null,
	email				varchar(64),
	calc_no_roles		integer not null,
	sex_id				bigint,
	date_cr				timestamp default now() not null,
	date_birth			date,
	im_icq				varchar(16),
	im_jabber			varchar(48),
	im_msn				varchar(48),
	im_skype			varchar(48),
	phone				varchar(48),
	date_last_page		timestamp,
	avatar_id			bigint,
	about				text,
	regsid				varchar(64) unique,
	prefer_l10n_id		bigint,
	foreign key			(sex_id) references Tsexes,
	foreign key			(avatar_id) references Tfiles,
	foreign key			(prefer_l10n_id) references Tl10n
);

insert into Tusers (login,first_name,calc_full_name,password,enabled,calc_no_roles) values ('admin','Administrator','Administrator','#password#',1,0);

alter table Tfiles add foreign key (user_cr_id) references Tusers;
alter table Tfile_folders add foreign key (user_cr_id) references Tusers;

/**************************************************************
 *
 */
create table Troles (
	id					bigserial primary key,
	code				varchar(32) not null unique
);
insert into Troles (code) values ('pages');
insert into Troles (code) values ('articles');
insert into Troles (code) values ('design');
insert into Troles (code) values ('settings');
insert into Troles (code) values ('files');
insert into Troles (code) values ('market');
insert into Troles (code) values ('guestbooks');
insert into Troles (code) values ('users');
insert into Troles (code) values ('admin_start');

create table Troles_l10n (
	id					bigserial primary key,
	fid_id				bigint not null,
	l10n_id				bigint not null,
	name				varchar(128) not null,
	foreign key			(fid_id) references Troles,
	foreign key			(l10n_id) references Tl10n
);
create unique index Iroles_l10n on Troles_l10n (fid_id,l10n_id);

/**************************************************************
 *
 */
create table Tusers_enrolled (
	id					bigserial primary key,
	user_id				bigint not null,
	role_id				bigint not null,
	foreign key			(user_id) references Tusers,
	foreign key			(role_id) references Troles
);
create unique index Iusers_enrolled on Tusers_enrolled (user_id,role_id);
create index Iusers_enrolled1  on Tusers_enrolled (user_id);

/**************************************************************
 *
 */
create table Tuser_sessions (
	id					bigserial primary key,
	user_id				bigint not null,
	sid					varchar(48) not null unique,
	date_exp			timestamp not null,
	foreign key			(user_id) references Tusers
);
create index Iuser_sessions1 on Tuser_sessions (date_exp);

/**************************************************************
 *
 */
create table Ttemplates (
	id					bigserial primary key,
	code				varchar(128) unique,
	name				varchar(128) not null unique
);

/**************************************************************
 *
 */
create table Ttemplate_place_types (
	id					bigserial primary key,
	code				varchar(16) not null unique,
	name				varchar(128) not null unique
);

/**************************************************************
 *
 */
create table Ttemplate_places (
	id					bigserial primary key,
	template_id			bigint not null,
	place_type_id		bigint not null,
	ordering			bigserial unique not null,
	code				varchar(16) not null,
	name				varchar(64),
	admin_height		integer default 20 not null,
	templatewide		integer default 0 not null,
	preprocess			integer default 0 not null,
	foreign key			(template_id) references Ttemplates,
	foreign key			(place_type_id) references Ttemplate_place_types
);
create unique index Itemplate_places on Ttemplate_places (template_id,code);

create table Ttemplate_places_templatewide (
	id					bigserial primary key,
	place_id			bigint not null,
	l10n_id				bigint not null,
	medium_id			bigint not null,
	body				text,
	foreign key			(place_id) references Ttemplate_places,
	foreign key			(l10n_id) references Tl10n,
	foreign key			(medium_id) references Tmediums
);
create unique index Itemplate_places_templatewide on Ttemplate_places_templatewide (place_id,medium_id,l10n_id);

/**************************************************************
 *
 */
create table Tpages (
	id					bigserial primary key,
	parent_id			bigint,
	alias				varchar(128) not null unique,
	template_id			bigint not null,
	prerun				varchar(2048),
	prerun_param		varchar(2048),
	show_in_nav			integer default 1 not null,
	ordering			bigserial not null unique,
	role_id				bigint null,
	hide_anon			integer default 0 not null,
	hide_reg			integer default 0 not null,
	cssclass			varchar(64),
	foreign key			(parent_id) references Tpages,
	foreign key			(template_id) references Ttemplates,
	foreign key			(role_id) references Troles
);
create index Ipages1 on Tpages (parent_id);
create unique index Ipages2 on Tpages (parent_id,ordering);

create table Tpages_l10n (
	id					bigserial primary key,
	fid_id				bigint not null,
	l10n_id				bigint not null,
	name				varchar(256),
	title				varchar(2048),
	meta_description	varchar(2048),
	meta_keywords		varchar(2048),
	disabled_l10n		integer default 0 not null,
	foreign key			(fid_id) references Tpages,
	foreign key			(l10n_id) references Tl10n
);
create unique index Ipages_l10n on Tpages_l10n (fid_id,l10n_id);
/**************************************************************
 *
 */
create table Tpage_blocks (
	id					bigserial primary key,
	page_id				bigint not null,
	l10n_id				bigint not null,
	place_id			bigint not null,
	medium_id			bigint not null,
	body				text,
	foreign key			(page_id) references Tpages,
	foreign key			(l10n_id) references Tl10n,
	foreign key			(place_id) references Ttemplate_places
);
create unique index Ipage_blocks on Tpage_blocks (page_id,l10n_id,medium_id,place_id);

/**************************************************************
 *
 */
create table Trewrites (
	id					bigserial primary key,
	regexp				varchar(256) not null unique,
	fields				varchar(128),
	page_id				bigint,
	controller_name		varchar(128),
	controller_mode		varchar(128),
	signature			varchar(128),
	foreign key			(page_id) references Tpages
);

/**************************************************************
 *
 */
create table Tmail_templates (
	id					bigserial primary key,
	code				varchar(256) not null unique,
	name				varchar(256),
	default_from		varchar(256),
	default_to			varchar(256),
	signature			varchar(128)
);

create table Tmail_templates_l10n (
	id					bigserial primary key,
	fid_id				bigint not null,
	l10n_id				bigint not null,
	subject				varchar(1024),
	body				text,
	foreign key			(fid_id) references Tmail_templates,
	foreign key			(l10n_id) references Tl10n
);
create unique index Imail_templates_l10n on Tmail_templates_l10n (fid_id,l10n_id);
/**************************************************************
 *
 */
create table Tarticle_categories (
	id					bigserial primary key,
	code				varchar(32) not null unique,
	groupcode			varchar(32),
	has_abstract		integer not null,
	has_image1			integer not null,
	has_image2			integer not null,
	has_gallery			integer not null,
	image1_id			bigint,
	image2_id			bigint,
	foreign key			(image1_id) references Tfiles,
	foreign key			(image2_id) references Tfiles
);
create index Tarticle_categories_groupcode_key on Tarticle_categories (groupcode);

create table Tarticle_categories_l10n (
	id					bigserial primary key,
	fid_id				bigint not null,
	l10n_id				bigint not null,
	name				varchar(64) not null,
	abstract			text,
	body				text,
	foreign key			(fid_id) references Tarticle_categories,
	foreign key			(l10n_id) references Tl10n
);
create unique index Iarticle_categories_l10n on Tarticle_categories_l10n (fid_id,l10n_id);

/**************************************************************
 *
 */
create table Tarticles (
	id					bigserial primary key,
	date_cr				timestamp default now() not null,
	date_use			date,
	date_show_start		date,
	date_show_end		date,
	category_id			bigint not null,
	calc_code			varchar(256) not null unique,
	image1_id			bigint,
	image2_id			bigint,
	image3_id			bigint,
	folder_id			bigint,
	enabled				integer not null,
	featured			integer default 0 not null,
	user_cr_id			bigint,
	foreign key			(category_id) references Tarticle_categories,
	foreign key			(image1_id) references Tfiles,
	foreign key			(image2_id) references Tfiles,
	foreign key			(image3_id) references Tfiles,
	foreign key			(folder_id) references Tfile_folders,
	foreign key			(user_cr_id) references Tusers
);

create table Tarticles_l10n (
	id					bigserial primary key,
	fid_id				bigint not null,
	l10n_id				bigint not null,
	name				varchar(4000) not null,
	abstract			text,
	body				text,
	disabled_l10n		integer default 0 not null,
	title				varchar(4000),
	meta_abstract		varchar(4000),
	meta_description	varchar(4000),
	foreign key			(fid_id) references Tarticles,
	foreign key			(l10n_id) references Tl10n
);
create unique index Iarticles_l10n on Tarticles_l10n (fid_id,l10n_id);

/**************************************************************
 *
 */
create table Tcustom_navs (
	id					bigserial primary key,
	code				varchar(128) not null unique,
	name				varchar(128) not null unique,
	can_href			integer not null,
	can_image			integer not null,
	can_description		integer not null
);

/**************************************************************
 *
 */
create table Tcustom_nav_items (
	id					bigserial primary key,
	nav_id				bigint,
	parent_id			bigint,
	image_id			bigint,
	href				varchar(256),
	page_id				bigint,
	enabled				integer default 1 not null,
	ordering			bigserial not null unique,
	foreign key			(nav_id) references Tcustom_navs,
	foreign key			(image_id) references Tfiles,
	foreign key			(page_id) references Tpages,
	foreign key			(parent_id) references Tcustom_nav_items
);
/*create unique index Icustom_nav_items on Tcustom_nav_items (nav_id,parent_id,name);*/

create table Tcustom_nav_items_l10n (
	id					bigserial primary key,
	fid_id				bigint not null,
	l10n_id				bigint not null,
	name				varchar(128),
	description			text,
	foreign key			(fid_id) references Tcustom_nav_items,
	foreign key			(l10n_id) references Tl10n
);
create unique index Icustom_nav_items_l10n on Tcustom_nav_items_l10n (fid_id,l10n_id);

/**************************************************************
 *	Content boxes groups
 */
create table Tcontent_boxes_groups (
	id					bigserial primary key,
	code				varchar(32) not null unique,
	name				varchar(256) not null unique
);

/**************************************************************
 *	Content boxes
 */
create table Tcontent_boxes (
	id					bigserial primary key,
	group_id			bigint not null,
	ordering			bigserial not null unique,
	image_id			bigint,
	image2_id			bigint,
	enabled				integer not null,
	foreign key			(group_id) references Tcontent_boxes_groups,
	foreign key			(image_id) references Tfiles,
	foreign key			(image2_id) references Tfiles
);

create table Tcontent_boxes_l10n (
	id					bigserial primary key,
	fid_id				bigint not null,
	l10n_id				bigint not null,
	override_image_id	bigint,
	name				varchar(1024),
	name2				varchar(1024),
	name3				varchar(1024),
	url					varchar(1024),
	foreign key			(fid_id) references Tcontent_boxes,
	foreign key			(l10n_id) references Tl10n,
	foreign key			(override_image_id) references Tfiles
);
create unique index Icontent_boxes_l10n on Tcontent_boxes_l10n (fid_id,l10n_id);

/**************************************************************
 *	Guestbooks
 */
create table Tguestbooks (
	id					bigserial primary key,
	code				varchar(32) not null unique,
	name				varchar(256) not null unique,
	premoderated		integer not null,
	users_can_post		integer not null,
	mail_code			varchar(128)
);

/**************************************************************
 *	Guestbook messages
 */
create table Tguestbook_messages (
	id					bigserial primary key,
	guestbook_id		bigint not null,
	user_q_name			varchar(128) not null,
	user_q_phone		varchar(128),
	user_q_email		varchar(128),
	user_q_avatar_id	bigint,
	user_a_id			bigint,
	question			text,
	answer				text,
	date_question		timestamp default now(),
	date_answer			timestamp,
	moderated			integer not null,
	foreign key			(guestbook_id) references Tguestbooks,
	foreign key			(user_q_avatar_id) references Tfiles,
	foreign key			(user_a_id) references Tusers
);

/**************************************************************
 *	Forms
 */
create table Tforms (
	id					bigserial primary key,
	code				varchar(32) not null unique,
	name				varchar(128) not null,
	fields_not_null		text,
	func_error			varchar(128),
	func_ok				varchar(1024),
	mail_template1_id	bigint,
	mail_template2_id	bigint,
	foreign key			(mail_template1_id) references Tmail_templates,
	foreign key			(mail_template2_id) references Tmail_templates
);



insert into Tusers_enrolled (user_id,role_id) select u.id,r.id from Tusers u,Troles r;


/**************************************************************
 *	spreadsheets
 */
create table Tspreadsheets (
	id					bigserial primary key,
	name				varchar(1000),
	file_id				bigint,
	date_cr				timestamp,
	generator			varchar(64),
	fid					bigint not null
);

create table Tworksheets (
	id					bigserial primary key,
	spreadsheet_id		bigint not null,
	name				varchar(1000),
	ordering			bigserial unique,
	foreign key			(spreadsheet_id) references Tspreadsheets
);

create table Tcells (
	id					bigserial primary key,
	worksheet_id		bigint not null,
	x					integer not null,
	y					integer not null,
	value				text,
	foreign key			(worksheet_id) references Tworksheets
);


/**************************************************************
 *	Market
 *		Categories
 */
create table Tmarket_categories (
	id					bigserial primary key,
	code				varchar(128) not null unique,
	parent_id			bigint,
	image1_id			bigint,
	image2_id			bigint,
	enabled				integer not null,
	ordering			bigserial unique,
	fid					varchar(32),
	is_featured			integer default 0 not null,
	is_new				integer default 0 not null,
	is_recomended		integer default 0 not null,
	has_abstract		integer default 0 not null,
	has_body2			integer default 0 not null,
	foreign key			(image1_id) references Tfiles,
	foreign key			(image2_id) references Tfiles,
	foreign key			(parent_id) references Tmarket_categories
);

create table Tmarket_categories_l10n (
	id					bigserial primary key,
	fid_id				bigint not null,
	l10n_id				bigint not null,
	name				varchar(256),
	abstract			text,
	body				text,
	body2				text,
	title				varchar(4000),
	meta_abstract		varchar(4000),
	meta_description	varchar(4000),
	foreign key			(fid_id) references Tmarket_categories,
	foreign key			(l10n_id) references Tl10n
);
create unique index Imarket_categories_l10n on Tmarket_categories_l10n (fid_id,l10n_id);

/**************************************************************
 *	Market
 *		Manufacturers
 */
create table Tmarket_manufacturers (
	id					bigserial primary key,
	code				varchar(128) not null unique,
	image1_id			bigint,
	image2_id			bigint,
	enabled				integer not null,
	ordering			bigserial unique,
	fid					varchar(32),
	is_featured			integer default 0 not null,
	is_new				integer default 0 not null,
	is_recomended		integer default 0 not null,
	foreign key			(image1_id) references Tfiles,
	foreign key			(image2_id) references Tfiles
);

create table Tmarket_manufacturers_l10n (
	id					bigserial primary key,
	fid_id				bigint not null,
	l10n_id				bigint not null,
	name				varchar(256),
	abstract			text,
	body				text,
	body2				text,
	title				varchar(4000),
	meta_abstract		varchar(4000),
	meta_description	varchar(4000),
	foreign key			(fid_id) references Tmarket_manufacturers,
	foreign key			(l10n_id) references Tl10n
);
create unique index Imarket_manufacturers_l10n on Tmarket_manufacturers_l10n (fid_id,l10n_id);

/**************************************************************
 *	Market
 *		Field groups, i.e. (for phones) Main section, Screen section, Multimedia support
 */
create table Tmarket_field_groups (
	id					bigserial primary key,
	ordering			bigserial unique,
	enabled				integer not null,
	category_id			bigint,
	foreign key			(category_id) references Tmarket_categories
);

create table Tmarket_field_groups_l10n (
	id					bigserial primary key,
	fid_id				bigint not null,
	l10n_id				bigint not null,
	name				varchar(256),
	foreign key			(fid_id) references Tmarket_field_groups,
	foreign key			(l10n_id) references Tl10n
);
create unique index Imarket_field_groups_l10n on Tmarket_field_groups_l10n (fid_id,l10n_id);

/**************************************************************
 *	Market
 *		View levels - DICTIONARY
 */
create table Tmarket_view_levels (
	id					bigserial primary key,
	code				varchar(32) not null unique
);
insert into Tmarket_view_levels (code) values ('no');
insert into Tmarket_view_levels (code) values ('list');
insert into Tmarket_view_levels (code) values ('short');
insert into Tmarket_view_levels (code) values ('full');

create table Tmarket_view_levels_l10n (
	id					bigserial primary key,
	fid_id				bigint not null,
	l10n_id				bigint not null,
	name				varchar(256),
	foreign key			(fid_id) references Tmarket_view_levels,
	foreign key			(l10n_id) references Tl10n
);
create unique index Imarket_view_levels_l10n on Tmarket_view_levels_l10n (fid_id,l10n_id);

/**************************************************************
 *	Market
 *		Search levels - DICTIONARY
 */
create table Tmarket_search_levels (
	id					bigserial primary key,
	code				varchar(32) not null unique
);
insert into Tmarket_search_levels (code) values ('no');
insert into Tmarket_search_levels (code) values ('basic');
insert into Tmarket_search_levels (code) values ('short');
insert into Tmarket_search_levels (code) values ('advanced');
insert into Tmarket_search_levels (code) values ('full');

create table Tmarket_search_levels_l10n (
	id					bigserial primary key,
	fid_id				bigint not null,
	l10n_id				bigint not null,
	name				varchar(256),
	foreign key			(fid_id) references Tmarket_search_levels,
	foreign key			(l10n_id) references Tl10n
);
create unique index Imarket_search_levels_l10n on Tmarket_search_levels_l10n (fid_id,l10n_id);

/**************************************************************
 *	Market
 *		Field datatypes
 */
create table Tmarket_field_datatypes (
	id					bigserial primary key,
	code				varchar(32) not null unique,
	view_code			varchar(32),
	name				varchar(128) not null unique,
	prefix				varchar(32),
	crsql				varchar(128),
	has_l10n			integer not null,
	has_enum			integer not null
);

/**************************************************************
 *	Market
 *		Fields, i.e. (for phones) Main section: standarts (gsm), type (smartphone), operating system (IOS)
 */
create table Tmarket_fields (
	id					bigserial primary key,
	group_id			bigint not null,
	datatype_id			bigint not null,
	view_level_id		bigint not null,
	search_level_id		bigint not null,
	enabled				integer not null,
	ordering			bigserial not null unique,
	cid					bigint,
	fid					varchar(128),
	foreign key			(group_id) references Tmarket_field_groups,
	foreign key			(datatype_id) references Tmarket_field_datatypes,
	foreign key			(view_level_id) references Tmarket_view_levels,
	foreign key			(search_level_id) references Tmarket_search_levels
);

create table Tmarket_fields_l10n (
	id					bigserial primary key,
	fid_id				bigint not null,
	l10n_id				bigint not null,
	name				varchar(256),
	foreign key			(fid_id) references Tmarket_fields,
	foreign key			(l10n_id) references Tl10n
);
create unique index Imarket_fields_l10n on Tmarket_fields_l10n (fid_id,l10n_id);

/**************************************************************
 *	Market
 *		item view levels
 */
create table Tmarket_item_view_levels (
	id					bigserial primary key,
	item_id				bigint not null,
	l10n_id				bigint not null,
	view_level_id		bigint not null,
	body				text,
	foreign key			(item_id) references Tmarket_items,
	foreign key			(l10n_id) references Tl10n,
	foreign key			(view_level_id) references Tmarket_view_levels
);
create unique index Imarket_item_view_levels on Tmarket_item_view_levels (item_id,l10n_id,view_level_id);

/**************************************************************
 *
 */
create table Tmarket_field_enums (
	id					bigserial primary key,
	field_id			bigint not null,
	value				varchar(2000),
	foreign key			(field_id) references Tmarket_fields
);
create unique index Imarket_field_enums on Tmarket_field_enums (field_id,value);

create table Tmarket_field_enumsl (
	id					bigserial primary key,
	field_id			bigint not null,
	l10n_id				bigint not null,
	value				varchar(2000),
	foreign key			(field_id) references Tmarket_fields,
	foreign key			(l10n_id) references Tl10n
);
create unique index Imarket_field_enumsl on Tmarket_field_enumsl (field_id,value);

/**************************************************************
 *
 */
create table Tmarket_item_groups (
	id					bigserial primary key,
	enabled				integer not null,
	ordering			bigserial unique
);

create table Tmarket_item_groups_l10n (
	id					bigserial primary key,
	fid_id				bigint not null,
	l10n_id				bigint not null,
	name				varchar(128),
	foreign key			(fid_id) references Tmarket_item_groups,
	foreign key			(l10n_id) references Tl10n
);
create unique index Imarket_item_groups_l10n on Tmarket_item_groups_l10n (fid_id,l10n_id);

/**************************************************************
 *
 */
create table Tmarket_items (
	id					bigserial primary key,
	code				varchar(256) not null unique,
	date_cr				timestamp default now(),
	image1_id			bigint,
	image2_id			bigint,
	group_id			bigint,
	category_id			bigint not null,
	manufacturer_id		bigint not null,
	enabled				integer not null,
	in_stock			integer not null,
	price				numeric(30,2),
	ordering			bigserial unique,
	is_featured			integer default 0 not null,
	is_new				integer default 0 not null,
	is_recomended		integer default 0 not null,
	fid					varchar(32),
	fid2				varchar(32),
	file_folder_id		bigint,
	old_price			numeric(30,2),
	base_price			numeric(30,2),
	discount_value		varchar(32),
	in_stock_count		integer,
	foreign key			(group_id) references Tmarket_item_groups,
	foreign key			(category_id) references Tmarket_categories,
	foreign key			(manufacturer_id) references Tmarket_manufacturers,
	foreign key			(image1_id) references Tfiles,
	foreign key			(image2_id) references Tfiles,
	foreign key			(file_folder_id) references Tfile_folders
);

create table Tmarket_items_l10n (
	id					bigserial primary key,
	fid_id				bigint not null,
	l10n_id				bigint not null,
	name				varchar(256),
	abstract			text,
	body				text,
	body2				text,
	foreign key			(fid_id) references Tmarket_items,
	foreign key			(l10n_id) references Tl10n
);
create unique index Imarket_items_l10n on Tmarket_items_l10n (fid_id,l10n_id);

/**************************************************************
 * Will dynamically add fields
 */
create table Tmarket_item_fields_i (
	id					bigserial primary key,
	item_id				bigint not null unique,
	foreign key			(item_id) references Tmarket_items
);

/**************************************************************
 * Will dynamically add fields
 */
create table Tmarket_item_fields_il (
	id					bigserial primary key,
	item_id				bigint not null unique,
	fid_id				bigint not null,
	foreign key			(item_id) references Tmarket_items,
	foreign key			(fid_id) references Tmarket_items
);

/**************************************************************
 * Will dynamically add fields
 */
create table Tmarket_item_fields_f (
	id					bigserial primary key,
	item_id				bigint not null unique,
	foreign key			(item_id) references Tmarket_items
);

/**************************************************************
 * Will dynamically add fields
 */
create table Tmarket_item_fields_s (
	id					bigserial primary key,
	item_id				bigint not null unique,
	foreign key			(item_id) references Tmarket_items
);

/**************************************************************
 * Will dynamically add fields
 */
create table Tmarket_item_fields_sl (
	id					bigserial primary key,
	item_id				bigint not null unique,
	fid_id				bigint not null,
	foreign key			(item_id) references Tmarket_items,
	foreign key			(fid_id) references Tmarket_items
);

/**************************************************************
 * 
 */
create table Tmarket_item_fields_enums (
	id					bigserial primary key,
	item_id				bigint not null,
	field_id			bigint not null,
	enum_id				bigint not null,
	foreign key			(item_id) references Tmarket_items,
	foreign key			(field_id) references Tmarket_fields,
	foreign key			(enum_id) references Tmarket_field_enums
);

/**************************************************************
 * 
 */
create table Tmarket_item_fields_enumsl (
	id					bigserial primary key,
	item_id				bigint not null,
	field_id			bigint not null,
	enum_id				bigint not null,
	foreign key			(item_id) references Tmarket_items,
	foreign key			(field_id) references Tmarket_fields,
	foreign key			(enum_id) references Tmarket_field_enumsl
);

/**************************************************************
 *
 */
create table Tcart_states (
	id					bigserial primary key,
	code				varchar(32) not null unique,
	cssclass			varchar(32),
	do_calc_money		integer not null
);

create table Tcart_states_l10n (
	id					bigserial primary key,
	fid_id				bigint not null,
	l10n_id				bigint not null,
	name				varchar(256),
	body				text,
	foreign key			(fid_id) references Tcart_states,
	foreign key			(l10n_id) references Tl10n
);


create table Tcart_payment_methods (
	id					bigserial primary key,
	code				varchar(32) not null unique,
	func				varchar(64),
	prepay				integer not null,
	enabled				integer not null
);

create table Tcart_payment_methods_l10n (
	id					bigserial primary key,
	fid_id				bigint not null,
	l10n_id				bigint not null,
	name				varchar(256),
	body				text,
	foreign key			(fid_id) references Tcart_payment_methods,
	foreign key			(l10n_id) references Tl10n
);

create table Tcarts (
	id					bigserial primary key,
	user_id				bigint,
	sid					varchar(128) unique,
	state_id			bigint not null,
	date_cr				timestamp default now(),
	date_mo				timestamp default now(),
	date_sent			timestamp,
	date_state_change	timestamp,
	payment_method_id	bigint,
	manager_id			bigint,
	calc_summ			numeric(30,2) default 0 not null,
	calc_items			integer default 0 not null,
	calc_amount			integer default 0 not null,
	address_postalindex	varchar(16),
	address_city		varchar(128),
	address_street		varchar(128),
	address_phone		varchar(64),
	address_comments	varchar(4000),
	address_nearest		varchar(128),
	address_flat		varchar(128),
	address_porch		varchar(128),
	address_floor		varchar(128),
	address_password	varchar(128),
	required_change		varchar(128),
	real_payed			numeric(30,2) default 0 not null,
	foreign key			(user_id)			references Tusers,
	foreign key			(state_id)			references Tcart_states,
	foreign key			(payment_method_id)	references Tcart_payment_methods,
	foreign key			(manager_id)		references Tusers
);

create table Tcart_items (
	id					bigserial primary key,
	cart_id				bigint not null,
	market_item_id		bigint,
	cnt					integer default 1 not null,
	other_ftype			varchar(128),
	other_fid			bigint,
	price				numeric(30,2) not null,
	amount				integer not null,
	foreign key			(cart_id) references Tcarts,
	foreign key			(market_item_id) references Tmarket_items,
	constraint Tcart_items_item_or_fid check (market_item_id is null or (other_ftype is null and other_fid is null))
);
create unique index Icart_items1 on Tcart_items (cart_id,market_item_id);
create unique index Icart_items2 on Tcart_items (cart_id,other_ftype,other_fid);


create table Tkvcache (
	id bigserial primary key,
	key varchar(4000) not null unique,
	value text
);
create or replace function Pcache_set(Lkey in varchar,Lvalue in text) returns void as $$
declare
    Lid bigint;
begin
    select max(id) into Lid from Tkvcache where key=Lkey;
    if Lid is not null then
        update Tkvcache set value=Lvalue where id=Lid;
    else
        insert into Tkvcache (key,value) values (Lkey,Lvalue);
    end if;
end;
$$ language plpgsql;

create table Tadmin_start_widgets (
	id				bigserial primary key,
	controller_name	varchar(64) not null,
	controller_mode	varchar(64) not null,
	fullscreen		integer default 0 not null,
	ordering		bigserial unique
);
insert into Tadmin_start_widgets (controller_name,controller_mode,fullscreen) values ('AdminStart','coolbg',1);
insert into Tadmin_start_widgets (controller_name,controller_mode,fullscreen) values ('AdminStart','wizard',0);

